package edu.ufl.cise.sanitas.database;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import edu.ufl.cise.sanitas.types.data.DailyLog;
import edu.ufl.cise.sanitas.types.data.Feed;
import edu.ufl.cise.sanitas.types.data.Newsfeed;
import edu.ufl.cise.sanitas.types.data.SleepInfo;
import edu.ufl.cise.sanitas.types.data.User;

/**
 * DataBaseManager can connect or modify a MySQL database which store user
 * and server information. 
 * 
 */

/**
 * @author tan
 * @version 1.0.0
 */

public class SanitasDBManager {

	private Connection conn;
	private Statement stmt = null;
	private java.util.Date today = new java.util.Date();
	private SimpleDateFormat dateFormator = new SimpleDateFormat("k:m MM/dd/yyyy");;

	public SanitasDBManager() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException ex) {
			ex.printStackTrace();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (IllegalAccessException ex) {
			ex.printStackTrace();
		}

		try {
			conn = java.sql.DriverManager
					.getConnection("jdbc:mysql://127.0.0.1/sanitas?user=root&password=google&useUnicode=true&characterEncoding=UTF-8");
		} catch (Exception ex) {
			ex.printStackTrace();
			System.out.println("cannot connect to the mysql server");
			System.exit(0);
		}
	}

	/**
	 * Disconnect to the local database
	 */
	public void disconnect() {
		try {
			conn.close();
			// in.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Create a user in database, then return user id from database
	 * 
	 * @param user
	 * @return userId
	 * @throws SQLException
	 */
	public int createUser(User user) {

		String name = user.getName();
		String pwd = user.getPassword();
		String gender = user.getGender();
		int age = user.getAge();
		int userId = 0;

		PreparedStatement createUser;
		try {
			createUser = conn
					.prepareStatement("INSERT INTO `sanitas`.`user` (`userId`, `userName` , `userPwd`, `userGender` ,	`userAge`) VALUES (?, ?, ?, ?, ?)");

			createUser.setString(1, null);
			createUser.setString(2, name);
			createUser.setString(3, pwd);
			createUser.setString(4, gender);
			createUser.setInt(5, age);
			createUser.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		userId = queryUserId(name, pwd);

		return userId;
	}

	// TODO need add user conditions
	/**
	 * Query profile for all users
	 * 
	 * @throws SQLException
	 */
	public void queryUserProfile() throws SQLException {

		stmt = conn
				.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.TYPE_FORWARD_ONLY);

		try {
			ResultSet srs = stmt.executeQuery("SELECT * FROM user");

			srs.afterLast();
			while (srs.previous()) {
				String name = srs.getString("userName");
				String gender = srs.getString("userGender");
				String age = srs.getString("userAge");
				// System.out.println(name + "     " + gender + "     " + age);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Query User information, then return them in a new User
	 * 
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public User queryUserProfle(int userId) {

		PreparedStatement queryProfile;
		try {
			queryProfile = conn.prepareStatement("SELECT * FROM `user` WHERE userId = ?");

			queryProfile.setInt(1, userId);

			ResultSet srs = queryProfile.executeQuery();

			while (srs.next()) {

				userId = srs.getInt("userId");
				String userName = srs.getString("userName");
				String userPwd = srs.getString("userPwd");
				String userGender = srs.getString("userGender");
				int userAge = srs.getInt("userAge");

				// System.out.println(userAge);

				User u = new User(userId, userName, userPwd, userGender, userAge);
				return u;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public String queryUserName(int userId) throws SQLException {

		PreparedStatement queryProfile = conn
				.prepareStatement("SELECT `userName` FROM `user` WHERE userId = ?");

		queryProfile.setInt(1, userId);

		ResultSet srs = queryProfile.executeQuery();

		while (srs.next()) {
			return srs.getString("userName");
		}
		return null;
	}

	public int queryUserId(String userName) throws SQLException {

		PreparedStatement queryProfile = conn
				.prepareStatement("SELECT `userId` FROM `user` WHERE userName = ?");

		queryProfile.setString(1, userName);

		ResultSet srs = queryProfile.executeQuery();

		while (srs.next()) {
			return srs.getInt("userId");
		}
		return 0;
	}

	public ArrayList<String> queryUserCondition(int userId) {

		ArrayList<String> condList = new ArrayList<String>();

		PreparedStatement queryProfile;
		try {
			queryProfile = conn
					.prepareStatement("SELECT `conditionName` FROM `conditions` WHERE `id` IN (SELECT `condId` FROM `userCondition` WHERE userId = ?)");

			queryProfile.setInt(1, userId);

			ResultSet srs = queryProfile.executeQuery();

			while (srs.next()) {
				condList.add(srs.getString("conditionName"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return condList;
	}

	public int queryUserId(String userName, String password) {

		int userId = 0;

		try {
			PreparedStatement queryId = conn
					.prepareStatement("SELECT `userId` FROM `user` WHERE `userName` = ? AND `userPwd` = ?");

			queryId.setString(1, userName);
			queryId.setString(2, password);
			ResultSet srs = queryId.executeQuery();
			srs.first();

			userId = srs.getInt("userId");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userId;
	}

	// /**
	// * login to the System thourgh name and pwd
	// *
	// * @param userName
	// * @throws SQLException
	// */
	// public int login(String userName, String password) {
	//	
	// int userId = 0;
	//		
	// try {
	// PreparedStatement queryId = conn
	// .prepareStatement("SELECT `userId` FROM `user` WHERE `userName` = ? AND `userPwd` = ?");
	//
	// queryId.setString(1, userName);
	// queryId.setString(2, password);
	// ResultSet srs = queryId.executeQuery();
	// srs.first();
	//
	// userId = srs.getInt("userId");
	//			
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	// return userId;
	// }

	/**
	 * Update user weighjt
	 * 
	 * @param userName
	 * @param weight
	 * @throws SQLException
	 */
	public void updateWeight(String userName, int w) {

		// System.out.println(srs.getString("userWeight"));

		try {
			PreparedStatement updateWeight = conn
					.prepareStatement("UPDATE user SET userWeight = ? WHERE userName = ?");
			updateWeight.setInt(1, w);
			updateWeight.setString(2, userName);
			updateWeight.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Query user daily log from database base on user name
	 * 
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public List<DailyLog> queryDailyLog(int userId) throws SQLException {

		List<DailyLog> dlist = new LinkedList<DailyLog>();

		PreparedStatement queryDayLog = conn
				.prepareStatement("SELECT * FROM dailyLog WHERE userID = ?");

		queryDayLog.setInt(1, userId);

		ResultSet srs = queryDayLog.executeQuery();

		while (srs.next()) {

			Date date = srs.getDate("date");
			int food = srs.getInt("food");
			int sport = srs.getInt("sport");
			int sleepHours = srs.getInt("sleepHours");

			DailyLog dlog = new DailyLog(date, userId, sport, food, new SleepInfo(sleepHours));
			dlist.add(dlog);
		}

		// System.out.println(dlist.size());
		return dlist;
	}

	/**
	 * usage: after user login, return related feed. 0 is general theme
	 * 
	 * @param userId
	 * @param userCond
	 * @return feedList
	 * @throws SQLException
	 */
	public List<Feed> queryFeed(int userId, int userCond) throws SQLException {

		List<Feed> feedList = new ArrayList<Feed>();

		// select * from feed where userId=1 or theme in (2, 0);
		PreparedStatement queryFeed = conn
				.prepareStatement("SELECT * FROM feed where userId = ? or theme in (?, 0);");

		queryFeed.setInt(0, userId);
		queryFeed.setInt(1, userCond);

		ResultSet srs = queryFeed.executeQuery();

		while (srs.next()) {

			int theme = srs.getInt(userCond); // theme id == user condition

			String conditionName = queryConditionName(theme);

			Timestamp ts = srs.getTimestamp("date");

			String content = srs.getString("content");

			String userName = queryUserName(userId);

			Feed feed = new Feed(ts.toString(), userName, conditionName, content);

			feedList.add(feed);
		}
		return feedList;
	}

	public Feed createNewFeed(String timeStamp, int userId, int themeId, String content) {

		// File image = new File("/home/tan/cd.png");

		// INSERT INTO `sanitas`.`feed` (
		// `theme` , `date` , `userId` , `content` )
		// VALUES ( '0', '2009-10-13', '0', 'test3' );

		// FileInputStream in = new FileInputStream(image);
		PreparedStatement newFeed;
		try {
			newFeed = conn
					.prepareStatement("INSERT INTO `feed` ( `theme`, `date`, `userId`, `content` ) VALUES ( ?, ?, ?, ? )");

			newFeed.setInt(1, themeId);

			newFeed.setTimestamp(2, new Timestamp(today.getTime()));

			newFeed.setInt(3, userId);
			newFeed.setString(4, content);

			newFeed.executeUpdate();

			return new Feed((new Timestamp(today.getTime())).toString(), queryUserName(userId),
					queryConditionName(themeId), content);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Query all related news feed based on userID
	 * 
	 * @param userId
	 * @return
	 */
	public Newsfeed queryUserAllFeeds(int userId) {

		// select * from feed where userId=1 or theme=0 or theme in
		// (select condId from userCondition where userId=1);

		ArrayList<Feed> feedList = new ArrayList<Feed>();

		// FileInputStream in = new FileInputStream(image);
		PreparedStatement userFeed;
		try {

			// select * from feed where userId=3 or theme=2;

			userFeed = conn.prepareStatement("select * from feed where userId = ? or "
					+ "theme=0 or theme in (select condId from userCondition where userId = ?)");

			userFeed.setInt(1, userId);
			userFeed.setInt(2, userId);

			ResultSet srs = userFeed.executeQuery();

			while (srs.next()) {

				int themeid = srs.getInt("theme");

				Timestamp ts = srs.getTimestamp("date");

				// System.out.println(ts.toString());

				int id = srs.getInt("userId");

				String userName = queryUserName(id);

				// System.out.println(userName);

				String content = srs.getString("content");

				String theme = queryConditionName(themeid);

				feedList.add(new Feed(ts.toString(), userName, theme, content));

			}

			// System.out.println(feedList.get(0).getContent());

			// System.out.println(feedList.size());

			Feed[] feedAry = new Feed[feedList.size()];

			for (int k = 0; k < feedList.size(); k++) {
				feedAry[k] = feedList.get(k);
			}

			// for (Feed f : feedAry) {
			// System.out.println(f.getContent());
			// }

			Newsfeed fetchedNewdFeed = new Newsfeed(feedAry);

			return fetchedNewdFeed;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public int queryConditionId(String condition) {

		try {
			PreparedStatement queryCondId = conn
					.prepareStatement("SELECT `id` FROM `conditions` WHERE `id` IN (SELECT `id` FROM `conditions` WHERE conditionName = ?)");
			queryCondId.setString(1, condition);
			ResultSet srs = queryCondId.executeQuery();

			while (srs.next()) {
				int condId = srs.getInt("id");

				return condId;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 0;
	}

	public boolean updateUserCondition(String userName, String userPwd, String[] conditions) {

		int userId;
		try {
			userId = queryUserId(userName);

			PreparedStatement delUserConds = conn
					.prepareStatement("DELETE FROM userCondition WHERE userId = ?");
			delUserConds.setInt(1, userId);

			delUserConds.execute();

			for (String cond : conditions) {

				// INSERT INTO `sanitas`.`userCondition` (
				// `userId` ,
				// `condId`
				// )
				// VALUES (
				// '1', '2'
				// );

				int condID = queryConditionId(cond);

				PreparedStatement insertUserConds = conn
						.prepareStatement("INSERT INTO userCondition (userId, condId) VALUES (?, ?)");
				insertUserConds.setInt(1, userId);
				insertUserConds.setInt(2, condID);

				insertUserConds.execute();
			}

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public String[] queryAllCondition() {

		ArrayList<String> condList = new ArrayList<String>();

		try {
			PreparedStatement queryCond = conn
					.prepareStatement("SELECT conditionName FROM conditions");

			ResultSet srs = queryCond.executeQuery();
			
			
			
			while (srs.next()) {
				condList.add(srs.getString("conditionName"));
			}
			
			
			String[] condAry = new String[condList.size()];
			
			
			for (int q = 0; q<condList.size(); q++) {
				condAry[q] = condList.get(q);
			}			
			
			
			return condAry;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;

	}

	public String queryConditionName(int ConditionId) {

		try {
			PreparedStatement queryCondId = conn
					.prepareStatement("SELECT `conditionName` FROM `conditions` WHERE id = ?");
			queryCondId.setInt(1, ConditionId);

			ResultSet srs = queryCondId.executeQuery();

			while (srs.next()) {
				String condName = srs.getString("conditionName");
				return condName;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "general";
	}

	public static void main(String[] argv) throws SQLException, IOException {

		SanitasDBManager sdbm = new SanitasDBManager();

		// sdbm.createUser(u);

		// sdbm.updateWeight("gao", 999);

		// sdbm.createUser(u);

		// sdbm.queryUserProfile();

		// sdbm.createNewFeed(2, 3, "with feed");

		// sdbm.queryUserAllFeeds(1);

		// System.out.println(sdbm.createUser(new User(-1, "cjz2", "cjz2", "m",
		// 30, 100, null)));

		// sdbm.queryUserCondition(1);

		// User u = new User("txt", "google", "m", 30);

		// sdbm.createUser(u);

		// sdbm.queryUserProfle(2);

		// sdbm.queryUserProfile();

		// sdbm.queryDailyLog(1);

		// System.out.println(sdbm.queryUserName(1));

		// sdbm.queryUserProfle(1);

		// sdbm.queryUserAllFeeds(5);

		// SimpleDateFormat bartDateFormat = new
		// SimpleDateFormat("k:m MM/dd/yyyy");
		//
		// java.util.Date today = new java.util.Date();
		// today = new Date(today.getTime());
		//
		// System.out.println(bartDateFormat.format(today));
		// System.out.println(today);

		// java.sql.Date sqlDate = new Date(date)
		
		System.out.println(sdbm.queryAllCondition().length);

		sdbm.disconnect();
	}
}